package com.xsy.middleware.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xsy.middleware.service.DataBackService;
import com.xsy.middleware.utils.ResultVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @Description: 定时同步crm数据到服务器
 * @Author: 黎炎松
 * @Date: 2024/4/19 14:42
 */
@Slf4j
@Controller
public class SyncCrmTableData {

    @Autowired
    private DataBackService dataBackService;

    /**
     * 根据表名处理数据同步
     * @param tableName
     * @return
     */
    public ResultVo syncCrmTableData(String tableName) {
        JSONObject tableData = new JSONObject();
        try {
            log.info("同步crm表格数据开始: {}", tableName);
            // 根据表名读取有变更的数据
            if (StringUtils.isBlank(tableName)) {
                log.info("表名不可为空");
                return ResultVo.error("表名不可为空");
            }

            // 判断库中表是否存在
            if (!dataBackService.isTableExist(tableName)) {
                log.info("同步数据失败，数据库中不存在当前表  ==== > {}", tableName);
                return ResultVo.error("同步数据失败, 数据库中不存在当前表 =====> " + tableName);
            }

            // 查询配置表信息
            tableData = dataBackService.getDataBackup(tableName);
            if (tableData == null) {
                log.info("查询配置表数据为空");
                return ResultVo.error("查询配置表数据为空");
            }

            // 获取上次修改时间、当前的时间
            Long startSyncTime = tableData.getLong("lastSyncTime__c");
            Long endSyncTime = System.currentTimeMillis();
            int addCount = 0;
            int updateCount = 0;

            // 查询配置表明细
            JSONArray tableFieldList = dataBackService.getDataBackupDetailByType(tableData.getLong("id"), 2);
            if (tableFieldList.size() <= 0) {
                log.info("当前配置表没有需要同步的数据字段 : {}", tableName);
                // 更新配置表数据
                updateDataBackup(tableData, addCount, updateCount, endSyncTime);
                return ResultVo.success("当前配置表没有需要同步的数据字段 : " + tableName);
            }

            // 获取查询的字段
            ArrayList<String> fieldList = new ArrayList<>();
            fieldList.add("id");
            for (Object o : tableFieldList) {
                JSONObject obj = (JSONObject) o;
                fieldList.add(obj.getString("fieldApiKey__c"));
            }
            String fileJoin = String.join(",", fieldList);

            // 查询crm距离上次同步时间内的修改或新增数据  获取上次修改时间、当前的时间
            String sql = "select %s from %s where updatedAt between %s and %s";
            sql = String.format(sql, fileJoin, tableName, startSyncTime, endSyncTime);
            JSONArray tableDataList = dataBackService.getCrmTableData(sql);
            if (tableDataList.size() <= 0) {
                log.info("暂无需要同步的数据 : {}", tableName);
                // 更新配置表数据
                updateDataBackup(tableData, addCount, updateCount, endSyncTime);
                return ResultVo.success("暂无需要同步的数据 ==== > " + tableName);
            }

            // 根据id在本地数据库进行查询
            List<String> idList = tableDataList.stream().map(v -> {
                JSONObject obj = (JSONObject) v;
                return obj.getString("id");
            }).collect(Collectors.toList());
            log.info("idList : {}", idList);

            sql = "select id from %s where id in (%s)";
            sql = String.format(sql, tableName, String.join(",", idList));
            List<Long> localIds = dataBackService.selectTableIds(sql);

            // 对数据进行判断是新增还是修改（后期优化为批量）
            log.info("开始对数据进行处理");
            for (Object o : tableDataList) {
                JSONObject tData = (JSONObject) o;
                Long tableId = Long.valueOf(tData.getString("id"));

                if (localIds.contains(tableId)) {
                    // 修改
                    String updateSql = getUpdateSql(tableId, tableName, fieldList, tData);
                    //log.info("执行修改 : {}", updateSql);
                    dataBackService.executeSql(updateSql);
                    updateCount++;
                    continue;
                }

                // 新增
                String insertSql = getInsertSql(tableName, fieldList, tData);
                //log.info("执行新增 : {}", insertSql);
                dataBackService.executeSql(insertSql);
                addCount++;
            }

            // 更新配置表数据
            updateDataBackup(tableData, addCount, updateCount, endSyncTime);

            log.info("更新配置表数据成功 : {}", tableName);
            return ResultVo.success("更新配置表数据成功 : " + tableName);

        } catch (Exception e) {
            log.error("定时同步crm表格数据处理异常 : {}", e.getMessage());
            //throw new RuntimeException(e);

            try {
                // 更新配置表数据
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("id", tableData.get("id"));
                jsonObject.put("syncStatus__c", 2);
                jsonObject.put("syncErrorDesc__c", e.getMessage());
                dataBackService.updateDataBackup(jsonObject);
            } catch (UnsupportedEncodingException ex) {
                throw new RuntimeException(ex);
            }

            return ResultVo.error("定时同步crm表格数据处理异常: " + e.getMessage());
        }
    }

    /**
     * 更新配置表更新信息
     * @param tableData
     * @param addCount
     * @param updateCount
     * @param endSyncTime
     * @return
     * @throws UnsupportedEncodingException
     */
    public ResultVo updateDataBackup(JSONObject tableData, int addCount, int updateCount, Long endSyncTime) throws UnsupportedEncodingException {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("id", tableData.get("id"));
        jsonObject.put("lastSyncAddCount__c", addCount);
        jsonObject.put("lastSyncUpdateCount__c", updateCount);
        jsonObject.put("lastSyncTime__c", endSyncTime);
        jsonObject.put("entityType", tableData.get("entityType"));
        jsonObject.put("syncStatus__c", 1);
        jsonObject.put("syncErrorDesc__c", "");
        ResultVo update = dataBackService.updateDataBackup(jsonObject);
        if (update.getCode() != 200) {
            log.info("更新配置表数据异常 : {}", update.getMsg());
            return ResultVo.error("更新配置表数据异常 ==== > " + update.getMsg());
        }
        return ResultVo.success();
    }

    /**
     * 获取修改语句
     *
     * @param tableId
     * @param tableName
     * @param fieldList
     * @param tData
     * @return
     */
    public String getUpdateSql (Long tableId, String tableName, ArrayList<String> fieldList, JSONObject tData) {
        // 有值 和 没值的的字段都需要进行修改
        ArrayList<String> uplateList = new ArrayList<>();
        for (String fieldKey : fieldList) {
            if (tData.get(fieldKey) != null) {
                String fieldValue = tData.getString(fieldKey);
                // 判断是否是反斜杠
                if (fieldValue.length() == 1 && fieldValue.charAt(0) == '\\') {
                    uplateList.add(fieldKey + " = " + "'\\\\'");
                } else {
                    uplateList.add(StringUtils.isNotBlank(fieldValue) ? fieldKey + " = '" + fieldValue + "'" : fieldKey + " = null");
                }
                continue;
            }
            uplateList.add(fieldKey + " = null");
        }
        String updateSql = "update %s set %s where id = %s;";
        return String.format(updateSql, tableName, String.join(",", uplateList), tableId);
    }


    public String getInsertSql (String tableName, ArrayList<String> fieldList, JSONObject tData) {
        // 获取对应字段值
        ArrayList<String> fieldValueList = new ArrayList<>();
        for (String fieldKey : fieldList) {
            if (tData.get(fieldKey) != null) {
                String fieldValue = tData.getString(fieldKey);
                // 判断是否是反斜杠
                if (fieldValue.length() == 1 && fieldValue.charAt(0) == '\\') {
                    fieldValueList.add("'\\\\'");
                } else {
                    fieldValueList.add(StringUtils.isNotBlank(fieldValue) ? "'"  + fieldValue + "'" : null);
                }
                continue;
            }
            fieldValueList.add(null);
        }
        String insertSql ="insert into %s (%s) values (%s);";
        return String.format(insertSql, tableName, String.join(",", fieldList), String.join(",", fieldValueList));
    }


}
